using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

namespace Test.Controls.App_Data
{
    public class DataAccess
    {
        public int GetPageCount()
        {
            using (SqlConnection conn = new SqlConnection("Data Source=192.168.1.188;User ID=sa;Password=Qwert!@#$%;Initial Catalog=Northwind"))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("select count(ProductID) from Products", conn);
                return (int)cmd.ExecuteScalar();
            }
        }
        public DataSet GetCustomList(int startRowIndex, int maximumRows)
        {
            using (SqlConnection conn = new SqlConnection("Data Source=192.168.1.188;User ID=sa;Password=Qwert!@#$%;Initial Catalog=Northwind"))
            {
                DataSet ds = new DataSet();
                try
                {
                    conn.Open();
                    string strSql = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY ProductID) AS RowNumber, * FROM Products) Results WHERE RowNumber between " + (startRowIndex + 1) + " and " + (startRowIndex + maximumRows);
                    SqlDataAdapter da = new SqlDataAdapter(strSql, conn);
                    ds = new DataSet();
                    da.Fill(ds);
                }
                catch (Exception e)
                {
                    string a = e.ToString();
                }
                return ds;
            }
        }
    }
}
